#delimit;
clear;
set more off;
capture log close;

capture erase /data/doed_pell_raw.dta;

!st /data/doed_pell_raw.sas7bdat /data/doed_pell_raw.dta

/*****
CHANGE PATH
*****/;
local in "/data";
local pathtab "/data";

use "`in'/doed_pell_raw.dta", clear;

format dis_dt %td;
label var dis_dt "last disbursement date";

label define match_criteria 1 "1=SSN, first 3 letters, and DOB" 2 "2=SSN and DOB" 3 "3=SSN and fisrt 3 letters" 4 "4=SSN only" 5 "5=First 3 letters and DOB";
label values match_criteria match_criteria;
label var match_criteria "how the information was matched";
drop if match_criteria==5;

/*I will drop if type==SG*/;
drop if type=="SG";
drop type;

/*I will drop those cases where the amount paid to date is 0*/
drop if amt_paid_to_dt==0;

rename tuedt2003 pid;

sort pid award_yr dis_dt;

preserve;
keep pid award_yr amt_paid_to_dt att_opeid;
gen opeid_1=substr(att_opeid,1,6);
gen opeid_2=substr(att_opeid,7,2);
gen opeid=opeid_1+"-"+opeid_2 if opeid_1~="";

keep pid opeid award_yr amt_paid_to_dt;

save "`pathtab'/temp_pell_tomerge.dta", replace;
restore;

/*I will keep the first observation per individual, which should reflect the first 
Pell received*/
by pid: keep if _n==1;

/*Making opeid compatible*/
gen opeid_1=substr(att_opeid,1,6);
gen opeid_2=substr(att_opeid,7,2);
gen opeid=opeid_1+"-"+opeid_2 if opeid_1~="";
drop opeid_2;

keep pid award_yr opeid_1 opeid;
rename opeid first_col_opeid_nslds;
rename opeid_1 first_col_opeid6_nslds;

sort pid;

gen year_enroll=award_yr;
replace year_enroll=1995 if award_yr<1995;

save "`pathtab'/temp_pell_tomerge_first_col.dta", replace;

merge m:1 first_col_opeid_nslds year_enroll using "`pathtab'/ipeds_state.dta";
keep if _merge==3;
drop idx _merge unitid;

save "`pathtab'/ipeds_state_merge_opeid8_pell_temp.dta", replace;

use "`pathtab'/temp_pell_tomerge_first_col.dta", clear;
sort first_col_opeid_nslds year_enroll;
merge m:1 first_col_opeid_nslds year_enroll using "`pathtab'/ipeds_state.dta";
keep if _merge==1;
drop _merge;
drop first_col_state first_col_state_fips first_col_sector;

merge m:1 first_col_opeid6_nslds year_enroll using "`pathtab'/ipeds_state_opeid6.dta";
keep if _merge==3;
drop _merge;

drop idx unitid first_col_opeid6_nslds;

append using "`pathtab'/ipeds_state_merge_opeid8_pell_temp.dta";

sort pid award_yr;

save "`pathtab'/ipeds_state_merge_opeid8_pell_temp.dta", replace;

use "`pathtab'/temp_pell_tomerge_first_col.dta", clear;
sort first_col_opeid_nslds year_enroll;
merge m:1 first_col_opeid_nslds year_enroll using "`pathtab'/ipeds_state.dta";
keep if _merge==1;
drop _merge;
drop first_col_state first_col_state_fips first_col_sector;

merge m:1 first_col_opeid6_nslds year_enroll using "`pathtab'/ipeds_state_opeid6.dta";
keep if _merge==1;
drop _merge;

drop idx unitid first_col_opeid6_nslds;
drop first_col_state first_col_state_fips first_col_sector;

merge m:1 first_col_opeid_nslds using "`pathtab'/ipeds_state_noyear.dta";
keep if _merge==3;
drop _merge;

append using "`pathtab'/ipeds_state_merge_opeid8_pell_temp.dta";

sort pid award_yr;

save "`pathtab'/ipeds_state_merge_opeid8_pell_temp.dta", replace;


use "`pathtab'/temp_pell_tomerge_first_col.dta", clear;
sort first_col_opeid_nslds year_enroll;
merge m:1 first_col_opeid_nslds year_enroll using "`pathtab'/ipeds_state.dta";
keep if _merge==1;
drop _merge;
drop first_col_state first_col_state_fips first_col_sector;

merge m:1 first_col_opeid6_nslds year_enroll using "`pathtab'/ipeds_state_opeid6.dta";
keep if _merge==1;
drop _merge;

drop idx unitid first_col_opeid6_nslds;
drop first_col_state first_col_state_fips first_col_sector;

merge m:1 first_col_opeid_nslds using "`pathtab'/ipeds_state_noyear.dta";
keep if _merge==1;
drop _merge;

drop first_col_state first_col_state_fips first_col_sector;

rename first_col_opeid_nslds opeid;
merge m:1 opeid using "`pathtab'/opeid_NSC.dta";
keep if _merge~=2;
drop _merge;

sort pid;
count if pid==pid[_n-1];

rename opeid first_col_opeid_nslds;
rename College_State first_col_state;

append using "`pathtab'/ipeds_state_merge_opeid8_pell_temp.dta";
drop year_enroll;

rename first_col_state first_col_state_pell;
rename first_col_state_fips first_col_state_fips_pell;
rename first_col_sector first_col_sector_pell;
rename first_col_opeid_nslds first_col_opeid_pell;
sort pid award_yr;
drop first_col_opeid6_nslds;

save "`pathtab'/first_col_pell_state.dta", replace;

erase "`pathtab'/ipeds_state_merge_opeid8_pell_temp.dta";


